<p>你好，我是林晓斌。</p><p>《MySQL实战45讲》这门课程已经全部结束了。我给你准备了一个结课小测试，来帮助你检验自己的学习效果。</p><p>这套测试题共有 20 道题目，包括3道单选题和17道多选题，满分 100 分，系统自动评分。</p><p>还等什么，点击下面按钮开始测试吧！</p><p><a href="http://time.geekbang.org/quiz/intro?act_id=109&amp;exam_id=232"><img src="https://static001.geekbang.org/resource/image/28/a4/28d1be62669b4f3cc01c36466bf811a4.png" alt=""></a></p><!-- [[[read_end]]] -->
<style>
    ul {
      list-style: none;
      display: block;
      list-style-type: disc;
      margin-block-start: 1em;
      margin-block-end: 1em;
      margin-inline-start: 0px;
      margin-inline-end: 0px;
      padding-inline-start: 40px;
    }
    li {
      display: list-item;
      text-align: -webkit-match-parent;
    }
    ._2sjJGcOH_0 {
      list-style-position: inside;
      width: 100%;
      display: -webkit-box;
      display: -ms-flexbox;
      display: flex;
      -webkit-box-orient: horizontal;
      -webkit-box-direction: normal;
      -ms-flex-direction: row;
      flex-direction: row;
      margin-top: 26px;
      border-bottom: 1px solid rgba(233,233,233,0.6);
    }
    ._2sjJGcOH_0 ._3FLYR4bF_0 {
      width: 34px;
      height: 34px;
      -ms-flex-negative: 0;
      flex-shrink: 0;
      border-radius: 50%;
    }
    ._2sjJGcOH_0 ._36ChpWj4_0 {
      margin-left: 0.5rem;
      -webkit-box-flex: 1;
      -ms-flex-positive: 1;
      flex-grow: 1;
      padding-bottom: 20px;
    }
    ._2sjJGcOH_0 ._36ChpWj4_0 ._2zFoi7sd_0 {
      font-size: 16px;
      color: #3d464d;
      font-weight: 500;
      -webkit-font-smoothing: antialiased;
      line-height: 34px;
    }
    ._2sjJGcOH_0 ._36ChpWj4_0 ._2_QraFYR_0 {
      margin-top: 12px;
      color: #505050;
      -webkit-font-smoothing: antialiased;
      font-size: 14px;
      font-weight: 400;
      white-space: normal;
      word-break: break-all;
      line-height: 24px;
    }
    ._2sjJGcOH_0 ._10o3OAxT_0 {
      margin-top: 18px;
      border-radius: 4px;
      background-color: #f6f7fb;
    }
    ._2sjJGcOH_0 ._3klNVc4Z_0 {
      display: -webkit-box;
      display: -ms-flexbox;
      display: flex;
      -webkit-box-orient: horizontal;
      -webkit-box-direction: normal;
      -ms-flex-direction: row;
      flex-direction: row;
      -webkit-box-pack: justify;
      -ms-flex-pack: justify;
      justify-content: space-between;
      -webkit-box-align: center;
      -ms-flex-align: center;
      align-items: center;
      margin-top: 15px;
    }
    ._2sjJGcOH_0 ._10o3OAxT_0 ._3KxQPN3V_0 {
      color: #505050;
      -webkit-font-smoothing: antialiased;
      font-size: 14px;
      font-weight: 400;
      white-space: normal;
      word-break: break-word;
      padding: 20px 20px 20px 24px;
    }
    ._2sjJGcOH_0 ._3klNVc4Z_0 {
      display: -webkit-box;
      display: -ms-flexbox;
      display: flex;
      -webkit-box-orient: horizontal;
      -webkit-box-direction: normal;
      -ms-flex-direction: row;
      flex-direction: row;
      -webkit-box-pack: justify;
      -ms-flex-pack: justify;
      justify-content: space-between;
      -webkit-box-align: center;
      -ms-flex-align: center;
      align-items: center;
      margin-top: 15px;
    }
    ._2sjJGcOH_0 ._3Hkula0k_0 {
      color: #b2b2b2;
      font-size: 14px;
    }
</style><ul><li>
<div class="_2sjJGcOH_0"><img src=""
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>Geek_732d8f</span>
  </div>
  <div class="_2_QraFYR_0">55分的差生飘过 ，得再刷一遍了</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-04-19 18:51:39</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/Q0j4TwGTfTJR5k5ThxFPZyew0UKNaq89utnIEQia2CiaxMOvacibGnbuZNpSiaaGTlRLWhmegxagELnIXfNveRxLfw/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>ping</span>
  </div>
  <div class="_2_QraFYR_0">40分，发誓再刷一遍，我就是个假的DBA</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-07-06 18:00:30</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src=""
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>helpme</span>
  </div>
  <div class="_2_QraFYR_0">老师有个问题一直没明白，希望能解惑一下<br>假设有这么一个语句select * from t where a=1 and b=2，a=1有100W的数据，但a=1同时b=2只有一条，那么联合索引(a,b)和(b,a)有区别吗？（这里不考虑有a、b单独查询的情况）<br>实际执行起来两者都很快，但从树的查找来看，(a,b)应该比(b,a)多扫描了树上100W的节点吧？我的理解是找到a=1，然后一直往后遍历了所有的a=1的节点，取出其中b=2的节点，不知道是否是这样的？那是否是索引(b,a)更效率呢？<br></div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-05-05 14:48:52</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/41/5a/6988ac7a.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>如月影</span>
  </div>
  <div class="_2_QraFYR_0">很疑惑，一个事务执行部分语句后碰到死锁，如果死锁检测选择其作为回滚事务不是回滚整个事务吗</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-04-17 19:59:01</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/12/d7/5f/428a79e3.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>Miles Shuai</span>
  </div>
  <div class="_2_QraFYR_0">第三题中 D选项 为啥不对？<br>如果是阻塞等待超时，会根据innodb_rollback_on_timeout配置判断是否回滚。<br>但是发生死锁时，肯定会回滚的。</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 题目是问 回滚事务哦😆</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-04-20 10:58:41</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/fc/18/1a7c3b56.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>正在研读Spring50</span>
  </div>
  <div class="_2_QraFYR_0">25分~我承认这里面还有赌的成分</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-05-28 15:57:33</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/16/9c/53/ade0afb0.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>ub8</span>
  </div>
  <div class="_2_QraFYR_0">第二遍刷~begin</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-05-11 19:22:18</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/32/0b/981b4e93.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>念你如昔</span>
  </div>
  <div class="_2_QraFYR_0">kill processlist id，不会回滚对应的事务吗？那杀长事务，不就是去innodb_trx表里面找到 processlist id去杀吗</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-05-11 15:46:31</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/1e/31/d6/4bfaa08c.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>阿川Arc</span>
  </div>
  <div class="_2_QraFYR_0">第六题A选项 insert(15,15)为什么会触发行锁呢</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-04-28 21:14:42</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/0f/c1/b7/e785c2c8.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>罗马的小脚丫</span>
  </div>
  <div class="_2_QraFYR_0">第三遍又开始了。</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-04-15 17:34:57</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src=""
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>xingshouzhyan</span>
  </div>
  <div class="_2_QraFYR_0">不知道还会不会回复。感觉存储过程没深入讲。遇到一个问题，完全不知道从哪里入手。请问，事务中调同一个存储过程多次，能这样用吗？这样调用，效率上是否比一个个调用存储过程好？存储过程中事务的提交是什么时候</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-03-18 16:48:10</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/20/c2/f8/fa471c6c.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>郝静华</span>
  </div>
  <div class="_2_QraFYR_0">我也是40，50分，虽然不是dba,看来还有好多知识点没彻底掌握</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-03-10 13:40:35</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/15/99/c3/e4f408d4.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>陌兮</span>
  </div>
  <div class="_2_QraFYR_0">感谢老师，老师辛苦了</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2021-02-24 19:26:46</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/1f/cc/a0/bd31d495.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>陆美芳</span>
  </div>
  <div class="_2_QraFYR_0">请教老师一个问题，今天遇到的：一个单机的MySQL实例【MySQL 5.7.26-log】，服务器昨晚11点突然宕机，今天上午10点开启后，MySQL也启动了,但是有报错，就是有个表因为表空间丢失所以被跳过了，然后这个表也打不开了，查看物理文件只看到ibd文件，日志看是因为意外宕机，导致数据不一致？具体怎么解释呀，原理还不是很懂，感觉老师讲过，但自己说不清，日志如下：<br>2020-11-19T10:13:39.660330+08:00 0 [ERROR] InnoDB: Space ID in fsp header is 2533997769, but in the page header it is 2390599045.<br>2020-11-19T10:13:39.660409+08:00 0 [ERROR] InnoDB: Data file ‘.&#47;dzfp_zzs_kpfw_arm&#47;t_user.ibd’ uses page size 2048, but the innodb_page_size start-up parameter is 16384<br>2020-11-19T10:13:39.660447+08:00 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.<br>2020-11-19T10:13:39.660459+08:00 0 [ERROR] InnoDB: The error means the system cannot find the path specified.<br>2020-11-19T10:13:39.660468+08:00 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.<br>2020-11-19T10:13:39.660484+08:00 0 [ERROR] InnoDB: Could not find a valid tablespace file for dzfp_zzs_kpfw_arm&#47;t_user. Please refer to http:&#47;&#47;dev.mysql.com&#47;doc&#47;refman&#47;5.7&#47;en&#47;innodb-troubleshooting-datadict.html for how to resolve the issue.<br>2020-11-19T10:13:39.660509+08:00 0 [Warning] InnoDB: Ignoring tablespace dzfp_zzs_kpfw_arm&#47;t_user because it could not be opened.</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-11-19 14:47:54</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/12/da/ec/779c1a78.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>往事随风，顺其自然</span>
  </div>
  <div class="_2_QraFYR_0">SELECT psj.id AS job_id,psj.serviceline_code,psj.staff_id, pms.mobile,pms.id,pms.fullname,pms.create_time,pms.gender,<br>         pms.nickname,pms.identity_id_type,pms.identity_id,pms.update_time,GROUP_CONCAT(CONCAT(pr.name,&#39; ( &#39;,psj.role_code) SEPARATOR &#39;,&#39;) AS role_name<br>        ,psj.role_code,pd.name AS dept_name,psj.dept_code,ps.name AS serviceline_name<br>        FROM pm_staff pms<br>        LEFT JOIN pm_staff_job psj ON psj.staff_id = pms.id AND psj.deleted=0 and  pms.deleted=0 and psj.check_status=1<br>        LEFT JOIN pm_role pr ON psj.role_code = pr.code AND psj.serviceline_code = pr.serviceline_code AND pr.deleted=0<br>        LEFT JOIN pm_serviceline ps ON psj.serviceline_code = ps.code AND ps.deleted=0<br>        LEFT JOIN pm_department pd ON psj.dept_code = pd.code AND psj.serviceline_code = pd.serviceline_code AND pd.deleted=0<br>        group by pms.id<br>        order by pms.update_time desc<br><br><br>这个order by不走索引，update_time字段建立了索引，left join的关联字段已经建立了索引，查询还是10s以上，有什么优化思路</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-07-09 16:27:13</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/bvIkMsUBo7glPEWJjpBEM7ice9nLySxsCibicZg2Uubuia27KZGXXYibPhMhIT6m1GVCIM1l1ILJnj74kwvOqqRSKjg/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>强强</span>
  </div>
  <div class="_2_QraFYR_0">感谢老师</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-07-08 19:59:13</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/1d/c2/f1/0cf79684.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>leichenlei</span>
  </div>
  <div class="_2_QraFYR_0">老师，请教两个问题：<br>	<br>    问题1：<br>        第6讲：server层有两种锁：1，表锁的语法是 lock tables … read&#47;write。2，MDL 不需要显式使用，在访问一个表的时候会被自动加上 。<br>        第19讲：复现 等待MDL锁：lock table t write。<br>        那么，lock table 写法到底是加表锁还是MDL锁呢？<br><br>    问题2：如何复现server层的“表锁”？如何复现innoDB引擎层的“表锁”呢? </div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-06-17 10:48:21</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/1d/12/13/e103a6e3.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>扩散性百万咸面包</span>
  </div>
  <div class="_2_QraFYR_0">老师，在这个比较少留言的地方问您一个问题：<br>WAL和普通的硬盘写应该就是顺序I&#47;O和随机I&#47;O的区别吧？因为可以把磁盘上专门某一块特定的地方用来写log file，所以顺序I&#47;O比随机I&#47;O要快？如果以后顺序I&#47;O和随机I&#47;O区别越来越小了，可以不可以在事务fsync的时候直接把更新写到对应的磁盘位置，而不是log file的磁盘位置呢？</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-05-25 21:54:12</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="http://thirdwx.qlogo.cn/mmopen/vi_32/oltLEqTrmHm2aJP99BK6tHu5h7hp4aj08wR5Wt6H31iadFduDAVvjYKmhQ2nvGbLV3lkVdiat2GRasgWXoJeTibUg/132"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>杨</span>
  </div>
  <div class="_2_QraFYR_0">40分不过还是学到了许多   主要主从那块不会  没接触过</div>
  <div class="_10o3OAxT_0">
    
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-04-29 23:57:57</div>
  </div>
</div>
</div>
</li>
<li>
<div class="_2sjJGcOH_0"><img src="https://static001.geekbang.org/account/avatar/00/14/2e/8c/b261e15a.jpg"
  class="_3FLYR4bF_0">
<div class="_36ChpWj4_0">
  <div class="_2zFoi7sd_0"><span>张滔</span>
  </div>
  <div class="_2_QraFYR_0">林老师，有个问题一直没明白，如果说数据行存储在主键索引的叶子节点，那么为什么还会存在全表扫描？扫描主键索引不就可以了吗？</div>
  <div class="_10o3OAxT_0">
    <p class="_3KxQPN3V_0">作者回复: 扫描主键索引就是“全表扫描“</p>
  </div>
  <div class="_3klNVc4Z_0">
    <div class="_3Hkula0k_0">2020-04-26 15:39:47</div>
  </div>
</div>
</div>
</li>
</ul>